package com.kitri.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.kitri.db.SetOfDataSource;
import com.kitri.domain.Certification;

public class CertificationDaoImpl implements CertificationDao {

	@Override
	public List<Certification> selectOfCertification(String seqId) {
		StringBuffer sql = new StringBuffer();

		sql.append("select certification.certification_id, certification.certification_name ");
		sql.append("from certification, member, juction_mb_cf ");
		sql.append("where certification.certification_id = juction_mb_cf.certification_id ");
		sql.append("and member.seq_id = juction_mb_cf.seq_id ");
		sql.append("and member.seq_id = ?");
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		List<Certification> list = new ArrayList<Certification>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql
				.toString(), new Object[] { seqId } );

		for (Map<String, Object> row : rows) {
			Certification certification = new Certification();

			certification.setCertificationId((String) row
					.get("CERTIFICATION_ID"));
			certification.setCertificationName((String) row
					.get("CERTIFICATION_NAME"));

			list.add(certification);
		}

		return list;
	}

	@Override
	public List<Certification> selectAll() {
		String sql = "SELECT * FROM CERTIFICATION";

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		List<Certification> list = new ArrayList<Certification>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);

		for (Map<String, Object> row : rows) {
			Certification certification = new Certification();

			certification.setCertificationId((String) row
					.get("CERTIFICATION_ID"));
			certification.setCertificationName((String) row
					.get("CERTIFICATION_NAME"));

			list.add(certification);
		}

		return list;
	}

	@Override
	public boolean insert(String certificationName) {
		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO CERTIFICATION (CERTIFICATION_ID, CERTIFICATION_NAME) ");
		sql.append("VALUES(concat(substr('F0000000', 1, 8-length(to_char(certification_seq.nextval))), ");
		sql.append("to_char(certification_seq.nextval)), ?)");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		int result = jdbcTemplate.update(sql.toString(),
				new Object[] { certificationName });

		if (result > 0)
			return true;
		return false;
	}

	@Override
	public boolean update(Certification certification) {
		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE CERTIFICATION SET CERTIFICATION_NAME = ? ");
		sql.append("WHERE CERTIFICATION_ID = ?");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		int result = jdbcTemplate.update(sql.toString(),
				new Object[] { certification.getCertificationName(),
						certification.getCertificationId() });

		if (result > 0)
			return true;
		return false;
	}

	@Override
	public boolean delete(String certificationId) {
		StringBuffer sql = new StringBuffer();

		sql.append("DELETE FROM CERTIFICATION ");
		sql.append("WHERE CERTIFICATION_ID = ?");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		int result = jdbcTemplate.update(sql.toString(),
				new Object[] { certificationId });

		if (result > 0)
			return true;
		return false;
	}
}
